﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;

namespace JobDescOnline.Presentation.Models
{
    public static class JobTitles
    {
        public static JobDescDBEntities1 db = new JobDescDBEntities1();
        public static dynamic RetrieveByFilterAndPage(List<FilterData> filters, int page, int pagesize, string sort, string direction)
        {
            var _temp = from j in db.JobTitles
                        join d in db.Departments on j.DepartmentID equals d.ID
                        select new JobTitleDepartment
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            DepartmentName = d.Description,
                            JobTitleName = j.Description,
                            MinEducation = j.MinEducation,
                            Major = j.Major,
                            MinExperience = j.MinExperience,
                            Specification = j.Specification,
                            FunctionalCompetency = j.FunctionalCompetency,
                            Challenge = j.Challenge,
                            Authority = j.Authority,
                            ImageOrganization = j.ImageOrganization,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(j => j.ID); break;
                    case "Department": _temp = _temp.OrderBy(j => j.DepartmentName); break;
                    case "Description": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "MinEducation": _temp = _temp.OrderBy(j => j.MinEducation); break;
                    case "Major": _temp = _temp.OrderBy(j => j.Major); break;
                    case "MinExperience": _temp = _temp.OrderBy(j => j.MinExperience); break;
                    case "Specification": _temp = _temp.OrderBy(j => j.Specification); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(j => j.ID); break;
                    case "Department": _temp = _temp.OrderByDescending(j => j.DepartmentName); break;
                    case "Description": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "MinEducation": _temp = _temp.OrderByDescending(j => j.MinEducation); break;
                    case "Major": _temp = _temp.OrderByDescending(j => j.Major); break;
                    case "MinExperience": _temp = _temp.OrderByDescending(j => j.MinExperience); break;
                    case "Specification": _temp = _temp.OrderByDescending(j => j.Specification); break;
                }
            }

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "Department": _temp = _temp.Where(j => j.DepartmentName.Contains(copy.value)); break;
                        case "Description": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "MinEducation": _temp = _temp.Where(j => j.MinEducation.Contains(copy.value)); break;
                        case "Major": _temp = _temp.Where(j => j.Major.Contains(copy.value)); break;
                        case "MinExperience": _temp = _temp.Where(j => j.MinExperience.Contains(copy.value)); break;
                        case "Specification": _temp = _temp.Where(j => j.Specification.Contains(copy.value)); break;
                    }
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static dynamic RetrieveByFilterAndPage(int page, int pagesize, string sort, string direction)
        {
            var _temp = from j in db.JobTitles
                        join d in db.Departments on j.DepartmentID equals d.ID
                        select new JobTitleDepartment
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            DepartmentName = d.Description,
                            JobTitleName = j.Description,
                            MinEducation = j.MinEducation,
                            Major = j.Major,
                            MinExperience = j.MinExperience,
                            Specification = j.Specification,
                            FunctionalCompetency = j.FunctionalCompetency,
                            Challenge = j.Challenge,
                            Authority = j.Authority,
                            ImageOrganization = j.ImageOrganization,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (direction.ToLower() == "asc")
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderBy(j => j.ID); break;
                    case "Department": _temp = _temp.OrderBy(j => j.DepartmentName); break;
                    case "Description": _temp = _temp.OrderBy(j => j.JobTitleName); break;
                    case "MinEducation": _temp = _temp.OrderBy(j => j.MinEducation); break;
                    case "Major": _temp = _temp.OrderBy(j => j.Major); break;
                    case "MinExperience": _temp = _temp.OrderBy(j => j.MinExperience); break;
                    case "Specification": _temp = _temp.OrderBy(j => j.Specification); break;
                }
            }
            else
            {
                switch (sort)
                {
                    case "ID": _temp = _temp.OrderByDescending(j => j.ID); break;
                    case "Department": _temp = _temp.OrderByDescending(j => j.DepartmentName); break;
                    case "Description": _temp = _temp.OrderByDescending(j => j.JobTitleName); break;
                    case "MinEducation": _temp = _temp.OrderByDescending(j => j.MinEducation); break;
                    case "Major": _temp = _temp.OrderByDescending(j => j.Major); break;
                    case "MinExperience": _temp = _temp.OrderByDescending(j => j.MinExperience); break;
                    case "Specification": _temp = _temp.OrderByDescending(j => j.Specification); break;
                }
            }

            return _temp.Skip((page - 1) * pagesize).Take(pagesize).ToList();
        }

        public static int RetrieveByFilter(List<FilterData> filters)
        {
            var _temp = from j in db.JobTitles
                        join d in db.Departments on j.DepartmentID equals d.ID
                        select new JobTitleDepartment
                        {
                            ID = j.ID,
                            Rowstatus = j.Rowstatus,
                            DepartmentName = d.Description,
                            JobTitleName = j.Description,
                            MinEducation = j.MinEducation,
                            Major = j.Major,
                            MinExperience = j.MinExperience,
                            Specification = j.Specification,
                            FunctionalCompetency = j.FunctionalCompetency,
                            Challenge = j.Challenge,
                            Authority = j.Authority,
                            ImageOrganization = j.ImageOrganization,
                            CreatedBy = j.CreatedBy,
                            CreatedOn = j.CreatedOn,
                            LastModifiedBy = j.LastModifiedBy,
                            LastModifiedOn = j.LastModifiedOn
                        };

            if (filters.Count > 0)
            {
                foreach (FilterData filter in filters)
                {
                    FilterData copy = filter;
                    switch (filter.column)
                    {
                        case "Department": _temp = _temp.Where(j => j.DepartmentName.Contains(copy.value)); break;
                        case "Description": _temp = _temp.Where(j => j.JobTitleName.Contains(copy.value)); break;
                        case "MinEducation": _temp = _temp.Where(j => j.MinEducation.Contains(copy.value)); break;
                        case "Major": _temp = _temp.Where(j => j.Major.Contains(copy.value)); break;
                        case "MinExperience": _temp = _temp.Where(j => j.MinExperience.Contains(copy.value)); break;
                        case "Specification": _temp = _temp.Where(j => j.Specification.Contains(copy.value)); break;
                    }
                }
            }
            
            return _temp.ToList().Count;
        }

        public static int RetrieveByFilter()
        {
            var list = db.JobTitles.ToList();
            return list.Count;
        }

        public static SelectList JobTitleCombo (int DepartmentID)
        {
            return new SelectList(db.JobTitles.Where(d => d.Rowstatus == true && d.DepartmentID == DepartmentID), "ID", "Description");
        }

        public static SelectList JobTitleID
        {
            get
            {
                return new SelectList(db.JobTitles.Where(d => d.Rowstatus == true), "ID", "Description");
            }
        }
    }

    public class JobTitleDepartment
    {
        public int ID { get; set; }
        public bool Rowstatus { get; set; }
        public string DepartmentName { get; set; }
        public string JobTitleName { get; set; }
        public string MinEducation { get; set; }
        public string Major { get; set; }
        public string MinExperience { get; set; }
        public string Specification { get; set; }
        public string FunctionalCompetency { get; set; }
        public string Challenge { get; set; }
        public string Authority { get; set; }
        public string ImageOrganization { get; set; }
        public string CreatedBy { get; set; }
        public Nullable<DateTime> CreatedOn { get; set; }
        public string LastModifiedBy { get; set; }
        public Nullable<DateTime> LastModifiedOn { get; set; }
    }
}